<?php

namespace app\admin\controller;

use support\Request;
use app\common\Attribute;
use app\model\Mysql;
use app\common\View;
use app\common\CurdBuild;
use app\model\Menu;

#[Attribute(title: "数据库")]
class MysqlController
{
    #[Attribute(isAuth: true, isPage: true, title: "查询")]
    public function query(Request $request)
    {
        $prefix = \config("thinkorm.connections.mysql.prefix"); //表前缀
        $data = selectDir(public_path("backup/"));
        $files = [];
        foreach ($data as $k => $v) {
            if (preg_match('/^' . $prefix . '/', $v)) {
                $arr = explode("-", $v);
                if (isset($arr[1])) {
                    $time = str_replace(".sql", "", $arr[1]);
                    // 将时间字符串转换为时间戳（需先通过substr分离出各个部分）
                    $timestamp = strtotime(substr($time, 0, 4) .
                        "-" . substr($time, 4, 2) . "-" .
                        substr($time, 6, 2) . " " .
                        substr($time, 8, 2) . ":" .
                        substr($time, 10, 2) . ":" .
                        substr($time, 12, 2));
                    // 将时间戳转换为指定格式的日期时间字符串
                    $formatted_time = date("Y-m-d H:i:s", $timestamp);
                    $files[] = [
                        "id" => $v,
                        "name" => str_replace($prefix, "", $arr[0]),
                        "time" => $formatted_time
                    ];
                } else {
                    continue;
                }
            } else {
                continue;
            }
        }
        View::assign(name: "files", value: $files);

        if ($request->method() === "POST") {

            $res = Mysql::SelectTable(field: "TABLE_NAME,TABLE_COMMENT,AUTO_INCREMENT");

            return json(['code' => 0, 'data' => $res]);
        }
        return View::fetch();
    }

    #[Attribute(isAuth: true, isPage: false, title: "一键菜单")]
    public function curd(Request $request)
    {
        $Menu_data = Menu::where('is_menu', 0)->where('pid', 0)->field('id,name')->select();
        View::assign(name: ["Menu_data" => $Menu_data]); //顶级选项
        $old_table = $request->get("table");
        if ($request->method() == "GET") {
            $prefix = \config("thinkorm.connections.mysql.prefix"); //表前缀
            $database = \config("thinkorm.connections.mysql.database"); //数据库名
            $res = Mysql::query("SELECT table_comment FROM information_schema.tables WHERE table_schema = '$database' AND table_name = '$prefix$old_table';");
            if ($res) {
                $res[0]["TABLE_NAME"] = $old_table;
                View::assign(name: "find", value: $res[0]);
            } else {
                return error(msg: "参数错误");
            }
        }
        if ($request->method() == "POST") {
            $all = $request->all();
            $res = CurdBuild::build($all);
            return $res;
        }
        return View::fetch();
    }

    #[Attribute(isAuth: true, isPage: true, title: "添加")]
    public function add(Request $request)
    {
        if ($request->method() === "POST") {
            $all = $request->all();
            try {
                validate("Mysql", "add", $all);
                $res = Mysql::CreateTable(
                    $all["table"],
                    $all["comment"],
                    $all["default_id"] == "true" ? true : false,
                    $all["default_cuTime"] == "true" ? true : false,
                    $all["default_del"] == "true" ? true : false
                );
                if ($res) {
                    return success("操作成功");
                } else {
                    return error("操作失败");
                }
            } catch (\think\exception\ValidateException $e) {
                return error(msg: "验证失败", data: $e->getError());
            }
        }
        return View::fetch();
    }

    #[Attribute(isAuth: true, isPage: false, title: "编辑")]
    public function edit(Request $request)
    {
        $old_table = $request->get("table");
        if ($request->method() == "GET") {
            $prefix = \config("thinkorm.connections.mysql.prefix"); //表前缀
            $database = \config("thinkorm.connections.mysql.database"); //数据库名
            $res = Mysql::query("SELECT table_comment FROM information_schema.tables WHERE table_schema = '$database' AND table_name = '$prefix$old_table';");
            if ($res) {
                $res[0]["TABLE_NAME"] = $old_table;
                View::assign(name: "find", value: $res[0]);
            } else {
                return error(msg: "参数错误");
            }
        }
        if ($request->method() == "POST") {
            $all = $request->all();
            try {
                validate("Mysql", "edit", $all);
                $res = Mysql::editTable($old_table, $all);
                if ($res) {
                    return success("操作成功", route("admin.mysql.edit") . "?table=" . $all["table"]);
                } else {
                    return error("操作失败");
                }
            } catch (\think\exception\ValidateException $e) {
                return error(msg: "验证失败", data: $e->getError());
            }
        }
        return View::fetch();
    }

    #[Attribute(isAuth: true, isPage: false, title: "删除")]
    public function del(Request $request)
    {
        if ($request->method() == "DELETE") {
            $all = $request->all();
            $dir = public_path("backup/"); //路径
            $prefix = \config("thinkorm.connections.mysql.prefix"); //表前缀
            if (!isset($all["table"])) {
                return error("参数错误");
            }
            $table = $prefix . $all["table"];
            $files = glob("$dir/$table*.sql");
            if (count($files) > 0) {
                $res = Mysql::delTable($all["table"]);
                if ($res) {
                    return success("操作成功");
                }
            } else {
                return error("该数据表没备份，请先备份!");
            }
        }
    }

    #[Attribute(isAuth: true, isPage: false, title: "备份")]
    public function backups(Request $request)
    {
        if ($request->method() == "POST") {
            $table = $request->post('table');
            $res = Mysql::backups($table);
            if ($res) {
                return success("操作成功");
            } else {
                return error("操作失败");
            }
        }
    }

    #[Attribute(isAuth: true, isPage: false, title: "还原")]
    public function restore(Request $request)
    {
        if ($request->method() == "POST") {
            $name = $request->post('name');
            $file = public_path("backup/" . $name);
            if (file_exists($file)) {
                Mysql::restore($file);
                return success("操作成功");
            } else {
                return error("参数错误");
            }
        }
    }

    #[Attribute(isAuth: true, isPage: false, title: "删除文件")]
    public function delFile(Request $request)
    {
        if ($request->method() == "DELETE") {
            $all = $request->all();
            $file = public_path("backup/" . $all["name"]);
            if (file_exists($file)) {
                if (unlink($file)) {
                    return success("操作成功");     // 删除成功，输出提示信息
                } else {
                    return error("操作失败");     // 删除失败，输出提示信息
                }
            } else {
                return error("参数错误");
            }
        }
    }

    #[Attribute(isAuth: true, isPage: false, title: "查询字段")]
    public function fields(Request $request)
    {
        if ($request->method() == "GET") {
            $table = $request->get(name: "table");
            View::assign(name: "table", value: $table);
        }

        if ($request->method() == "POST") {
            $all = $request->all();
            $find = Mysql::selectField(
                table: $all["table"],
                field: "COLUMN_NAME,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT,COLUMN_DEFAULT"
            );
            if ($find) {
                return json(['code' => 0, 'data' => $find]);
            } else {
                return error(msg: "参数错误");
            }
        }
        return View::fetch();
    }

    #[Attribute(isAuth: true, isPage: false, title: "添加字段")]
    public function addField(Request $request)
    {
        if ($request->method() == "POST") {
            $all = $request->all();
            if (strpos($all["limit"], ',') !== false) {
                $all["limit"] = explode(",", $all["limit"]);
            }
            $res = Mysql::audField($all["table"], [$all]);
            if ($res) {
                return success("操作成功", route("admin.mysql.addField") . "?table=" . $all["table"]);
            } else {
                return error("操作失败");
            }
        }
        return View::fetch();
    }

    #[Attribute(isAuth: true, isPage: false, title: "编辑字段")]
    public function editField(Request $request)
    {
        $oldField = $request->get("field");
        if ($request->method() == "GET") {
            $table = $request->get("table");
            $prefix = \config("thinkorm.connections.mysql.prefix"); //表前缀
            $sql = "SELECT
                COLUMN_NAME,
                COLUMN_TYPE,
                COLUMN_DEFAULT,
                COLUMN_COMMENT 
            FROM
                INFORMATION_SCHEMA.COLUMNS 
            WHERE
                TABLE_NAME = '" . $prefix . $table . "' 
                AND COLUMN_NAME = '" . $oldField . "';";
            $find = Mysql::query($sql);
            if ($find) {
                preg_match('/\((.*?)\)/', $find[0]["COLUMN_TYPE"], $matches);
                if (isset($matches[1])) {
                    $find[0]["COLUMN_LIMIT"] = str_replace("'", "", $matches[1]);
                } else {
                    $find[0]["COLUMN_LIMIT"] = "";
                }
                $find[0]["COLUMN_TYPE"] = preg_replace('/\([^\)]*\)/', '', $find[0]["COLUMN_TYPE"]);
                View::assign("find", $find[0]);
            } else {
                return error("参数错误");
            }
        }
        if ($request->method() == "POST") {
            $all = $request->all();

            if (strpos($all["limit"], ',') !== false) {
                $all["limit"] = explode(",", $all["limit"]);
            }

            $res = Mysql::audField($all["table"], [$all], "CHANGE COLUMN ");
            if ($res) {
                return success("操作成功", route("admin.mysql.editField") . "?table=" . $all["table"] . "&field=" . $all["old_field"]);
            } else {
                return error("操作失败");
            }
        }
        return View::fetch();
    }

    #[Attribute(isAuth: true, isPage: false, title: "删除字段")]
    public function delField(Request $request)
    {
        if ($request->method() == "DELETE") {
            $all = $request->all();
            $res = Mysql::audField($all["table"], [$all["field"]], "DROP COLUMN");
            if ($res) {
                return success("操作成功");
            } else {
                return error("操作失败");
            }
        }
    }
}
